﻿CREATE PROCEDURE [dbo].[admin_dailyTimeReport] AS
BEGIN

    SET NOCOUNT ON
   
    DECLARE @reportDate AS DATETIME
    IF DATENAME(dw,GETDATE()) = 'Monday' 
     BEGIN
      SET @reportDate = GETDATE()-3
     END
    ELSE
     BEGIN
      SET @reportDate = GETDATE()-1
     END
SET     @reportDate = GETDATE()
    SELECT
      CAST(u.screenName AS VARCHAR(10)) AS 'Screen Name',
      SUM(tl.duration)/60 AS 'Hours Logged'
    FROM
      timeLog tl
      INNER JOIN users u ON u.userID = tl.userId
      INNER JOIN companyUser cu ON cu.userID = u.userID
    WHERE
      cu.companyId = 10 AND
      u.userID != 1 AND
      CONVERT(VARCHAR(10),tl.createDate,110) = CONVERT(VARCHAR(10),@reportDate,110)
    GROUP BY
      u.screenName

	CREATE TABLE #timeSummary
	(
		roleId INT,
		name NVARCHAR(50),
		hoursLogged DECIMAL(10,2)
	)

    INSERT INTO #timeSummary
    SELECT 
      at.roleId,
      at.name,
      CASE 
        WHEN SUM(tl.duration) = 0 THEN CAST(0 AS DECIMAL(10,2))
        ELSE CAST(SUM(tl.duration)/60 AS DECIMAL(10,2)) END AS hoursLogged
    FROM
      timeLog tl
      INNER JOIN users u ON u.userID = tl.userId
      INNER JOIN companyUser cu ON cu.userID = u.userID
      INNER JOIN activityType at ON at.activityTypeId = tl.activityTypeId
    WHERE
      cu.companyId = 10 AND
      u.userID != 1 AND
      CONVERT(VARCHAR(10),tl.createDate,110) = CONVERT(VARCHAR(10),@reportDate,110)
    GROUP BY
      at.name,
      at.roleId
      

    SELECT
      CAST(r.name AS VARCHAR(15)) AS 'Role Name',
      CAST(ts1.name AS VARCHAR(15)) AS 'Activity Type',  
      CAST(CAST(((ts1.hoursLogged/ts2.totalHours)*100) AS DECIMAL(10,1))AS VARCHAR(5)) + '%'   AS 'Percentage Of Role'
    FROM
     #timeSummary ts1
    INNER JOIN 
     (
        SELECT 
            SUM(ts.hoursLogged) as totalHours, 
            ts.roleId 
        FROM 
            #timeSummary ts  
        GROUP BY 
            ts.roleId
     ) ts2 ON ts1.roleId = ts2.roleId
    INNER JOIN role r ON r.roleId = ts1.roleId
    ORDER BY r.roleId, 3 DESC,2

    DROP TABLE #timeSummary

SELECT 
      CONVERT(VARCHAR(10),u.screenName) AS ScreenName,
      CONVERT(VARCHAR(15),r.name) AS Role,
      CONVERT(VARCHAR(15),at.name) AS ActivityType,
      tl.duration AS Minutes,
      CONVERT(VARCHAR(50),tl.description) AS Description,
      SUBSTRING(CONVERT(VARCHAR(20),tl.createDate,100),13,7) AS Time
    FROM
      timeLog tl
      INNER JOIN users u ON u.userID = tl.userId
      INNER JOIN companyUser cu ON cu.userID = u.userID
      INNER JOIN activityType at ON at.activityTypeId = tl.activityTypeId
      INNER JOIN role r ON r.roleId = at.roleId
    WHERE
      cu.companyId = 10 AND
      u.userID != 1 AND
      CONVERT(VARCHAR(10),tl.createDate,110) = CONVERT(VARCHAR(10),@reportDate,110)
ORDER BY
  u.screenName,
  tl.createDate
END